package classes;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class SubjectManager {
	static Statement statement;
	private static final String subTable = "subjects";
	private static final String subLecturer = "subject_vs_lecturer";
	public static String SUBJECTMANAGER = "subjectManager";

	public SubjectManager(Statement statement) {
		this.statement = statement;
	}
	
	public boolean createSubject(String subjectName, String sylabus){
		try{
			int row = statement.executeUpdate("insert into " + subTable + " values(null, '" + subjectName + "','" + sylabus + "')");
			return(row > 0);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return false;
	}
	
	public static boolean addLecturersToSubject(int subjectID, String lecturerusername){
		
		try {
			int row = statement.executeUpdate("insert into " + subLecturer + " values(" + subjectID + "," +  AccountManager.getID(lecturerusername) + ")");
			return (row > 0);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	/*
	 * public void setLecturerToSubject(String subject_name, Integer
	 * lecturer_id) { try { statement.executeUpdate("Update " + subTable +
	 * " set " + "syllabus_url" + "='" + syllabus_Url + "' where subject_id='" +
	 * subject_id + "';"); } catch (SQLException e) { e.printStackTrace(); } }
	 */

	public static void setSyllabusUrl(Integer subject_id, String syllabus_Url) {
		try {
			statement.executeUpdate("Update " + subTable + " set "
					+ "syllabus_url" + "='" + syllabus_Url
					+ "' where subject_id='" + subject_id + "';");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static String getSubjectName(Integer subject_id) {
		try {
			ResultSet rs = statement.executeQuery("Select *from " + subTable
					+ " where subject_id=" + subject_id + ";");
			rs.next();
			return rs.getString("subject_name");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public int getSubjectId(String subject_name){
		try {
			ResultSet rs = statement.executeQuery("Select subject_id from " + subTable + " where subject_name='" + subject_name + "\';");
			rs.next();
			return rs.getInt("subject_id");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}

	public Subject getSubject(Integer subject_id) {
		try {
			ResultSet rs = statement.executeQuery("Select *from " + subTable
					+ " where subject_id='" + subject_id + "';");
			rs.next();
			int id = rs.getInt("subject_id");
			String subject_name = rs.getString("subject_name");
			String syllabus_url = rs.getString("syllabus_url");
			Subject subject = new Subject(id, subject_name, syllabus_url);
			return subject;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Subject getSubject(String subname){
		try {
			ResultSet rs = statement.executeQuery("Select *from " + subTable
					+ " where subject_name='" + subname + "'");
			rs.next();
			int id = rs.getInt("subject_id");
			String subject_name = rs.getString("subject_name");
			String syllabus_url = rs.getString("syllabus_url");
			Subject subject = new Subject(id, subject_name, syllabus_url);
			
			rs = statement.executeQuery("select lecturer_id from " + subLecturer + " where lecturer_id = " + id);
			while(rs.next()){
				subject.addLecturerFromSubjectManager(AccountManager.getUsername(rs.getInt(1)));
			}
			
			return subject;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<Integer> getLecturerSubject(Integer lecturer_id) {
		ArrayList<Integer> arr = new ArrayList<Integer>();
		try {
			ResultSet rs;
			rs = statement.executeQuery("Select *from " + subLecturer
					+ " where lecturer_id=" + lecturer_id + ");");
			while (rs.next()) {
				arr.add(rs.getInt("subject_id"));
			}
			return arr;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;

	}
	
	
	public Subject[] getAllSubjects(){
		try {
			ResultSet rs = statement.executeQuery("Select * from " + subTable);
			rs.last();
			Subject[] subjects = new Subject[rs.getRow()];
			rs.beforeFirst();
			for(int i=0; rs.next(); i++){
				int id = rs.getInt("subject_id");
				String subject_name = rs.getString("subject_name");
				String syllabus_url = rs.getString("syllabus_url");
				subjects[i] = new Subject(id, subject_name, syllabus_url);
			}
			return subjects;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;

	}
	

	public static void updateSomethingString(Integer subject_id, String columnName,
			String value) {
		try {
			statement
					.executeUpdate("Update " + subTable + " set " + columnName
							+ "='" + value + "' where subject_id='"
							+ subject_id + "';");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void updateSomethingInt(Integer subject_id, String columnName,
			int value) {
		try {
			statement
					.executeUpdate("Update " + subTable + " set " + columnName
							+ "=" + value + " where subject_id='"
							+ subject_id + "';");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public void deleteSubject(String subname){
		try {
			int id = getSubjectId(subname);
			statement.executeUpdate("delete from subjects where subject_name='" + subname + "';");
			statement.executeUpdate("delete from subject_vs_lecturer where subject_id = " + id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void deleteLecturerFromSubject(int subjectID, String lecturerusername){
		try {
			statement.executeUpdate("delete from subject_vs_lecturer where subject_id=" + subjectID + " and lecturer_id = " + AccountManager.getID(lecturerusername) + "");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}
